/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;
import DTO.NguoiDung_DTO;
import DTO.SanPham_DTO;

import DTO.SanPham_DTO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author xxx
 */
public class NguoiDung_DAO {
    
    Database db = new Database();
    Connection conn = null;
    PreparedStatement pst = null;
    
    
    // lay danh sach khach hang
    public ArrayList<NguoiDung_DTO> Lay_DsKhachHang() throws Exception
    {
        ArrayList<NguoiDung_DTO> arr = new ArrayList<NguoiDung_DTO>(); 
        String sql = "SELECT * FROM NguoiDung where LoaiND = 2";       
        conn = db.open();
        pst = conn.prepareStatement(sql);
        
        ResultSet rs = pst.executeQuery();
        
        while (rs.next())
        {
            NguoiDung_DTO kh = new NguoiDung_DTO();   
            kh.setId(rs.getInt("IDNguoiDung"));
            kh.setHoTen(rs.getString("HoTen"));
            kh.setSdt(rs.getInt("SDT"));
            kh.setNgaySinh(rs.getDate("NgaySinh"));
            kh.setDiaChi(rs.getString("DiaChi"));
            kh.setGioiTinh(rs.getInt("GioiTinh"));
            kh.setUsername(rs.getString("UserName"));
            kh.setMatKhau(rs.getString("matKhau"));
            kh.setLoaiND(rs.getInt("LoaiND"));
            arr.add(kh);
        }
        db.close();
        return arr;
        
    }
    
    // Xoa khach hang
    public int XoaKH(int id) throws Exception
    {
         String sql = "DELETE NguoiDung WHERE IDNguoiDung=?";
         conn = db.open(); 
         pst = conn.prepareStatement(sql);
         
         pst.setInt(1, id);
         
         int kq = pst.executeUpdate();
         
         db.close();
         return kq;
    }
    
    // Them khach hang
    
    public void ThemKhachHang(NguoiDung_DTO kh) throws Exception
    {
        // Loai nguoi dung: 1:khach hang
         String sql = "INSERT INTO NguoiDung (HoTen,SDT,NgaySinh,DiaChi,GioiTinh,UserName,MatKhau,2) VALUES (?,?,?,?,?,?)";
         conn = db.open(); 
         pst = conn.prepareStatement(sql);
         
         pst.setInt(1, kh.getSdt());
         pst.setDate(2, kh.getNgaySinh());
         pst.setString(3, kh.getDiaChi());
         pst.setInt(4, kh.getGioiTinh());
         pst.setString(5, kh.getUsername());
         pst.setString(6, kh.getMatKhau());
                 
         pst.executeUpdate();
         
         db.close();
    }
    
    // Tim kiem khach hang theo ten
    
    public ArrayList<NguoiDung_DTO> Timkiemkhachhang_theoten(String ten) throws Exception
    {
        ArrayList<NguoiDung_DTO> arr = new ArrayList<NguoiDung_DTO>(); 
        String sql = "SELECT * FROM NguoiDung WHERE HoTen LIKE '" + ten +"%'" ; 
        
        
        conn = db.open();
        pst = conn.prepareStatement(sql);
        
        ResultSet rs = pst.executeQuery();
        
        while (rs.next())
        {
            NguoiDung_DTO kh = new NguoiDung_DTO();  
            kh.setId(rs.getInt("IDNguoiDung"));
            kh.setHoTen(rs.getString("HoTen"));
            kh.setSdt(rs.getInt("SDT"));
            kh.setNgaySinh(rs.getDate("NgaySinh"));
            kh.setDiaChi(rs.getString("DiaChi"));
            kh.setGioiTinh(rs.getInt("GioiTinh"));
            kh.setUsername(rs.getString("UserName"));
            kh.setMatKhau(rs.getString("matKhau"));
            kh.setLoaiND(rs.getInt("LoaiND"));
            arr.add(kh);
        }               
        db.close();
        return arr;
    }
    
    
}
